



           

using System;
using System.Collections.Generic;
using System.Web;
using System.Data.Common;
using Shohdi.DataHandler;
using System.Data;




namespace DatabaseComponent
{
    
    public partial class PersonsDB
    {
        /// <summary>
        /// this function will get the count of persons in the system
        /// </summary>
        /// <param name="con">the connection</param>
        /// <returns>the count of persons</returns>
        public long getPersonCount(ClassDataHandler con)
        {
            DataTable dtCount = con.SelectInDataTable("select count(*) from dbo.[Persons] where dbo.[Persons].StatusID <> '2' ", CommandType.Text);
            if (dtCount.Rows.Count == 0)
                return 0;
            else
            {
                return long.Parse(dtCount.Rows[0][0].ToString());
            }
        }


        /// <summary>
        /// this function will get the count of persons in the system
        /// </summary>
        /// <param name="con">the connection</param>
        /// <returns>the count of persons</returns>
        public long getPersonCount()
        {
            ClassDataHandler con = new ClassDataHandler(_Connection,_Provider);
            con.OpenConnection();

            long lRet = getPersonCount(con);


            con.CloseConnection();


            return lRet;
        }


        /// <summary>
        /// this function will get the employees under  a manager
        /// </summary>
        /// <param name="con">the connection of the database of type class data handler</param>
        /// <param name="ManagerID">the manager id</param>
        /// <returns>a list of all his employees</returns>
        public List<Persons> SelectManagerEmployees (ClassDataHandler con, long ManagerID)
        {

            DbParameter dbGenID =  con.createParameter ();
            dbGenID.ParameterName = "@GenID";
            dbGenID.DbType = DbType.Int64;
            dbGenID.Value = ManagerID;


            DbDataReader dr = con.SelectDataReader("dbo.getManagerEmployees", CommandType.StoredProcedure, dbGenID );

            List<Persons> lstRet = new List<Persons>();

            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    Persons obj = new Persons();
                    if (dr["GenID"] != DBNull.Value)
                    {
                        obj.GenID = (System.Int64)dr["GenID"];
                    }
                    else
                    {
                        obj.GenID = -1;
                    }
                    if (dr["Name_AR"] != DBNull.Value)
                    {
                        obj.Name_AR = (System.String)dr["Name_AR"];
                    }
                    else
                    {
                        obj.Name_AR = "";
                    }
                    if (dr["Name_EN"] != DBNull.Value)
                    {
                        obj.Name_EN = (System.String)dr["Name_EN"];
                    }
                    else
                    {
                        obj.Name_EN = "";
                    }
                    if (dr["MiddleName_AR"] != DBNull.Value)
                    {
                        obj.MiddleName_AR = (System.String)dr["MiddleName_AR"];
                    }
                    else
                    {
                        obj.MiddleName_AR = "";
                    }
                    if (dr["MiddleName_EN"] != DBNull.Value)
                    {
                        obj.MiddleName_EN = (System.String)dr["MiddleName_EN"];
                    }
                    else
                    {
                        obj.MiddleName_EN = "";
                    }
                    if (dr["FamilyName_AR"] != DBNull.Value)
                    {
                        obj.FamilyName_AR = (System.String)dr["FamilyName_AR"];
                    }
                    else
                    {
                        obj.FamilyName_AR = "";
                    }
                    if (dr["FamilyName_EN"] != DBNull.Value)
                    {
                        obj.FamilyName_EN = (System.String)dr["FamilyName_EN"];
                    }
                    else
                    {
                        obj.FamilyName_EN = "";
                    }
                    if (dr["Gender"] != DBNull.Value)
                    {
                        obj.Gender = (System.Boolean)dr["Gender"];
                    }
                    else
                    {
                        obj.Gender = false;
                    }
                    if (dr["Badge"] != DBNull.Value)
                    {
                        obj.Badge = (System.String)dr["Badge"];
                    }
                    else
                    {
                        obj.Badge = "";
                    }
                    if (dr["ID"] != DBNull.Value)
                    {
                        obj.ID = (System.String)dr["ID"];
                    }
                    else
                    {
                        obj.ID = "";
                    }
                    if (dr["BirthDate"] != DBNull.Value)
                    {
                        obj.BirthDate = (System.DateTime?)dr["BirthDate"];
                    }
                    else
                    {
                        obj.BirthDate = null;
                    }
                    if (dr["RelationID"] != DBNull.Value)
                    {
                        obj.RelationID = (System.Int64?)dr["RelationID"];
                    }
                    else
                    {
                        obj.RelationID = null;
                    }
                    if (dr["NationalityID"] != DBNull.Value)
                    {
                        obj.NationalityID = (System.Int64?)dr["NationalityID"];
                    }
                    else
                    {
                        obj.NationalityID = null;
                    }
                    if (dr["OperationAreaID"] != DBNull.Value)
                    {
                        obj.OperationAreaID = (System.Int64?)dr["OperationAreaID"];
                    }
                    else
                    {
                        obj.OperationAreaID = null;
                    }
                    if (dr["PersonTypeID"] != DBNull.Value)
                    {
                        obj.PersonTypeID = (System.Int64?)dr["PersonTypeID"];
                    }
                    else
                    {
                        obj.PersonTypeID = null;
                    }
                    if (dr["ChargeAccount"] != DBNull.Value)
                    {
                        obj.ChargeAccount = (System.String)dr["ChargeAccount"];
                    }
                    else
                    {
                        obj.ChargeAccount = "";
                    }
                    if (dr["OrgChartID"] != DBNull.Value)
                    {
                        obj.OrgChartID = (System.Int64?)dr["OrgChartID"];
                    }
                    else
                    {
                        obj.OrgChartID = null;
                    }
                    if (dr["Email"] != DBNull.Value)
                    {
                        obj.Email = (System.String)dr["Email"];
                    }
                    else
                    {
                        obj.Email = "";
                    }
                    if (dr["Password"] != DBNull.Value)
                    {
                        obj.Password = (System.String)dr["Password"];
                    }
                    else
                    {
                        obj.Password = "";
                    }
                    if (dr["UserTypeID"] != DBNull.Value)
                    {
                        obj.UserTypeID = (System.Int64)dr["UserTypeID"];
                    }
                    else
                    {
                        obj.UserTypeID = -1;
                    }
                    if (dr["Mobile1"] != DBNull.Value)
                    {
                        obj.Mobile1 = (System.String)dr["Mobile1"];
                    }
                    else
                    {
                        obj.Mobile1 = "";
                    }
                    if (dr["Mobile2"] != DBNull.Value)
                    {
                        obj.Mobile2 = (System.String)dr["Mobile2"];
                    }
                    else
                    {
                        obj.Mobile2 = "";
                    }
                    if (dr["Phone1"] != DBNull.Value)
                    {
                        obj.Phone1 = (System.String)dr["Phone1"];
                    }
                    else
                    {
                        obj.Phone1 = "";
                    }
                    if (dr["InnerPhone1"] != DBNull.Value)
                    {
                        obj.InnerPhone1 = (System.String)dr["InnerPhone1"];
                    }
                    else
                    {
                        obj.InnerPhone1 = "";
                    }
                    if (dr["Phone2"] != DBNull.Value)
                    {
                        obj.Phone2 = (System.String)dr["Phone2"];
                    }
                    else
                    {
                        obj.Phone2 = "";
                    }
                    if (dr["InnerPhone2"] != DBNull.Value)
                    {
                        obj.InnerPhone2 = (System.String)dr["InnerPhone2"];
                    }
                    else
                    {
                        obj.InnerPhone2 = "";
                    }
                    if (dr["Language"] != DBNull.Value)
                    {
                        obj.Language = (System.Int64?)dr["Language"];
                    }
                    else
                    {
                        obj.Language = null;
                    }
                    if (dr["StatusID"] != DBNull.Value)
                    {
                        obj.StatusID = (System.Int64)dr["StatusID"];
                    }
                    else
                    {
                        obj.StatusID = -1;
                    }
                    if (dr["EmploymentStatus"] != DBNull.Value)
                    {
                        obj.EmploymentStatus = (System.Int64?)dr["EmploymentStatus"];
                    }
                    else
                    {
                        obj.EmploymentStatus = null;
                    }
                    if (dr["EducationLevel"] != DBNull.Value)
                    {
                        obj.EducationLevel = (System.Int64)dr["EducationLevel"];
                    }
                    else
                    {
                        obj.EducationLevel = -1;
                    }
                    if (dr["PositionEmail"] != DBNull.Value)
                    {
                        obj.PositionEmail = (System.String)dr["PositionEmail"];
                    }
                    else
                    {
                        obj.PositionEmail = "";
                    }
                    if (dr["OfficePhone"] != DBNull.Value)
                    {
                        obj.OfficePhone = (System.String)dr["OfficePhone"];
                    }
                    else
                    {
                        obj.OfficePhone = "";
                    }
                    if (dr["OfficeInnerPhone"] != DBNull.Value)
                    {
                        obj.OfficeInnerPhone = (System.String)dr["OfficeInnerPhone"];
                    }
                    else
                    {
                        obj.OfficeInnerPhone = "";
                    }



                    lstRet.Add(obj);


                }
            }

            dr.Close();


            return lstRet;
        }

        /// <summary>
        /// this function will get the employees under  a manager
        /// </summary>
        /// <param name="ManagerID">the manager id</param>
        /// <returns>a list of all his employees</returns>
        public List<Persons> SelectManagerEmployees(long ManagerID)
        {
            ClassDataHandler con = new ClassDataHandler(_Connection, _Provider);

            con.OpenConnection();

            List<Persons> lstRet = SelectManagerEmployees(con, ManagerID);

            con.CloseConnection();

            return lstRet;
        }




        
        
        
        
        
        //Write any additional methods or properties here 
          //Don't modify any genrated classes

        /// <summary>
        /// This will get the top 100 persons ordered by NameEN
        /// </summary>
        /// <returns>a list of the persons returned</returns>
        public List<Persons> getTop100PersonsOrderByNameEN()
        {
            ClassDataHandler con = new ClassDataHandler(_Connection);
            con.OpenConnection();

            List<Persons> lstPersons = getTop100PersonsOrderByNameEN(con);

            con.CloseConnection();

            return lstPersons;
        }


        /// <summary>
        /// this will get the top 100 persons ordered by NameEN
        /// </summary>
        /// <param name="con">the database handler having the connection</param>
        /// <returns>a list of persons returned</returns>
        public List<Persons> getTop100PersonsOrderByNameEN (ClassDataHandler con)
        {
            
            List<Persons> lstRet = new List<Persons>();


            


            lstRet = Select(con, " TOP 100 ", " where [StatusID]<>'2' order by [Name_EN] ");

            

            return lstRet;

        }




        /// <summary>
        /// this function will search by using or and (like for strings)
        /// </summary>
        /// <param name="obj">the object containing values to search by</param>
        /// <returns>a list of persons returned</returns>
        public List<Persons> SearchLikeOROrderByNAME_EN( Persons obj)
        {
            ClassDataHandler con = new ClassDataHandler(_Connection);
            con.OpenConnection();

            List<Persons> lstPersons = SearchLikeOROrderByNAME_EN(con, obj);


            con.CloseConnection();

            return lstPersons;
        }


        /// <summary>
        /// this function will search by using or and (like for strings)
        /// </summary>
        /// <param name="con">the database handler having the connection</param>
        /// <param name="obj">the object containing values to search by</param>
        /// <returns>a list of persons returned</returns>
        public List<Persons> SearchLikeOROrderByNAME_EN(ClassDataHandler con, Persons obj)
        {
            string strWhere = "";
            List<DbParameter> lstParameters = new List<DbParameter>();


            if (obj.GenID != -1)
            {
                DbParameter prmGenID = con.createParameter();
                prmGenID.ParameterName = "@GenID";
                prmGenID.DbType = DbType.Int64;
                if (obj.GenID == null)
                {
                    prmGenID.Value = DBNull.Value;
                }
                else
                {
                    prmGenID.Value = obj.GenID;
                }
                strWhere += " [GenID]=@GenID OR ";
                lstParameters.Add(prmGenID);
            }





            if (obj.Name_AR != "")
            {
                DbParameter prmName_AR = con.createParameter();
                prmName_AR.ParameterName = "@Name_AR";
                prmName_AR.DbType = DbType.String;
                if (obj.Name_AR == null)
                {
                    prmName_AR.Value = DBNull.Value;
                }
                else
                {
                    prmName_AR.Value = obj.Name_AR;
                }
                strWhere += " [Name_AR] LIKE @Name_AR OR ";
                lstParameters.Add(prmName_AR);
            }





            if (obj.Name_EN != "")
            {
                DbParameter prmName_EN = con.createParameter();
                prmName_EN.ParameterName = "@Name_EN";
                prmName_EN.DbType = DbType.String;
                if (obj.Name_EN == null)
                {
                    prmName_EN.Value = DBNull.Value;
                }
                else
                {
                    prmName_EN.Value = obj.Name_EN;
                }
                strWhere += " [Name_EN] LIKE @Name_EN OR ";
                lstParameters.Add(prmName_EN);
            }





            if (obj.MiddleName_AR != "")
            {
                DbParameter prmMiddleName_AR = con.createParameter();
                prmMiddleName_AR.ParameterName = "@MiddleName_AR";
                prmMiddleName_AR.DbType = DbType.String;
                if (obj.MiddleName_AR == null)
                {
                    prmMiddleName_AR.Value = DBNull.Value;
                }
                else
                {
                    prmMiddleName_AR.Value = obj.MiddleName_AR;
                }
                strWhere += " [MiddleName_AR] LIKE @MiddleName_AR OR ";
                lstParameters.Add(prmMiddleName_AR);
            }





            if (obj.MiddleName_EN != "")
            {
                DbParameter prmMiddleName_EN = con.createParameter();
                prmMiddleName_EN.ParameterName = "@MiddleName_EN";
                prmMiddleName_EN.DbType = DbType.String;
                if (obj.MiddleName_EN == null)
                {
                    prmMiddleName_EN.Value = DBNull.Value;
                }
                else
                {
                    prmMiddleName_EN.Value = obj.MiddleName_EN;
                }
                strWhere += " [MiddleName_EN] LIKE @MiddleName_EN OR ";
                lstParameters.Add(prmMiddleName_EN);
            }





            if (obj.FamilyName_AR != "")
            {
                DbParameter prmFamilyName_AR = con.createParameter();
                prmFamilyName_AR.ParameterName = "@FamilyName_AR";
                prmFamilyName_AR.DbType = DbType.String;
                if (obj.FamilyName_AR == null)
                {
                    prmFamilyName_AR.Value = DBNull.Value;
                }
                else
                {
                    prmFamilyName_AR.Value = obj.FamilyName_AR;
                }
                strWhere += " [FamilyName_AR] LIKE @FamilyName_AR OR ";
                lstParameters.Add(prmFamilyName_AR);
            }





            if (obj.FamilyName_EN != "")
            {
                DbParameter prmFamilyName_EN = con.createParameter();
                prmFamilyName_EN.ParameterName = "@FamilyName_EN";
                prmFamilyName_EN.DbType = DbType.String;
                if (obj.FamilyName_EN == null)
                {
                    prmFamilyName_EN.Value = DBNull.Value;
                }
                else
                {
                    prmFamilyName_EN.Value = obj.FamilyName_EN;
                }
                strWhere += " [FamilyName_EN] LIKE @FamilyName_EN OR ";
                lstParameters.Add(prmFamilyName_EN);
            }





            if (obj.Gender != false)
            {
                DbParameter prmGender = con.createParameter();
                prmGender.ParameterName = "@Gender";
                prmGender.DbType = DbType.Boolean;
                if (obj.Gender == null)
                {
                    prmGender.Value = DBNull.Value;
                }
                else
                {
                    prmGender.Value = obj.Gender;
                }
                strWhere += " [Gender]=@Gender OR ";
                lstParameters.Add(prmGender);
            }





            if (obj.Badge != "")
            {
                DbParameter prmBadge = con.createParameter();
                prmBadge.ParameterName = "@Badge";
                prmBadge.DbType = DbType.String;
                if (obj.Badge == null)
                {
                    prmBadge.Value = DBNull.Value;
                }
                else
                {
                    prmBadge.Value = obj.Badge;
                }
                strWhere += " [Badge] LIKE @Badge OR ";
                lstParameters.Add(prmBadge);
            }





            if (obj.ID != "")
            {
                DbParameter prmID = con.createParameter();
                prmID.ParameterName = "@ID";
                prmID.DbType = DbType.String;
                if (obj.ID == null)
                {
                    prmID.Value = DBNull.Value;
                }
                else
                {
                    prmID.Value = obj.ID;
                }
                strWhere += " [ID] LIKE @ID OR ";
                lstParameters.Add(prmID);
            }





            if (obj.BirthDate != null)
            {
                DbParameter prmBirthDate = con.createParameter();
                prmBirthDate.ParameterName = "@BirthDate";
                prmBirthDate.DbType = DbType.DateTime;
                if (obj.BirthDate == null)
                {
                    prmBirthDate.Value = DBNull.Value;
                }
                else
                {
                    prmBirthDate.Value = obj.BirthDate;
                }
                strWhere += " [BirthDate]=@BirthDate OR ";
                lstParameters.Add(prmBirthDate);
            }





            if (obj.RelationID != null)
            {
                DbParameter prmRelationID = con.createParameter();
                prmRelationID.ParameterName = "@RelationID";
                prmRelationID.DbType = DbType.Int64;
                if (obj.RelationID == null)
                {
                    prmRelationID.Value = DBNull.Value;
                }
                else
                {
                    prmRelationID.Value = obj.RelationID;
                }
                strWhere += " [RelationID]=@RelationID OR ";
                lstParameters.Add(prmRelationID);
            }





            if (obj.NationalityID != null)
            {
                DbParameter prmNationalityID = con.createParameter();
                prmNationalityID.ParameterName = "@NationalityID";
                prmNationalityID.DbType = DbType.Int64;
                if (obj.NationalityID == null)
                {
                    prmNationalityID.Value = DBNull.Value;
                }
                else
                {
                    prmNationalityID.Value = obj.NationalityID;
                }
                strWhere += " [NationalityID]=@NationalityID OR ";
                lstParameters.Add(prmNationalityID);
            }





            if (obj.OperationAreaID != null)
            {
                DbParameter prmOperationAreaID = con.createParameter();
                prmOperationAreaID.ParameterName = "@OperationAreaID";
                prmOperationAreaID.DbType = DbType.Int64;
                if (obj.OperationAreaID == null)
                {
                    prmOperationAreaID.Value = DBNull.Value;
                }
                else
                {
                    prmOperationAreaID.Value = obj.OperationAreaID;
                }
                strWhere += " [OperationAreaID]=@OperationAreaID OR ";
                lstParameters.Add(prmOperationAreaID);
            }





            if (obj.PersonTypeID != null)
            {
                DbParameter prmPersonTypeID = con.createParameter();
                prmPersonTypeID.ParameterName = "@PersonTypeID";
                prmPersonTypeID.DbType = DbType.Int64;
                if (obj.PersonTypeID == null)
                {
                    prmPersonTypeID.Value = DBNull.Value;
                }
                else
                {
                    prmPersonTypeID.Value = obj.PersonTypeID;
                }
                strWhere += " [PersonTypeID]=@PersonTypeID OR ";
                lstParameters.Add(prmPersonTypeID);
            }





            if (obj.ChargeAccount != "")
            {
                DbParameter prmChargeAccount = con.createParameter();
                prmChargeAccount.ParameterName = "@ChargeAccount";
                prmChargeAccount.DbType = DbType.String;
                if (obj.ChargeAccount == null)
                {
                    prmChargeAccount.Value = DBNull.Value;
                }
                else
                {
                    prmChargeAccount.Value = obj.ChargeAccount;
                }
                strWhere += " [ChargeAccount] LIKE @ChargeAccount OR ";
                lstParameters.Add(prmChargeAccount);
            }





            if (obj.OrgChartID != null)
            {
                DbParameter prmOrgChartID = con.createParameter();
                prmOrgChartID.ParameterName = "@OrgChartID";
                prmOrgChartID.DbType = DbType.Int64;
                if (obj.OrgChartID == null)
                {
                    prmOrgChartID.Value = DBNull.Value;
                }
                else
                {
                    prmOrgChartID.Value = obj.OrgChartID;
                }
                strWhere += " [OrgChartID]=@OrgChartID OR ";
                lstParameters.Add(prmOrgChartID);
            }





            if (obj.Email != "")
            {
                DbParameter prmEmail = con.createParameter();
                prmEmail.ParameterName = "@Email";
                prmEmail.DbType = DbType.String;
                if (obj.Email == null)
                {
                    prmEmail.Value = DBNull.Value;
                }
                else
                {
                    prmEmail.Value = obj.Email;
                }
                strWhere += " [Email] LIKE @Email OR ";
                lstParameters.Add(prmEmail);
            }





            if (obj.Password != "")
            {
                DbParameter prmPassword = con.createParameter();
                prmPassword.ParameterName = "@Password";
                prmPassword.DbType = DbType.String;
                if (obj.Password == null)
                {
                    prmPassword.Value = DBNull.Value;
                }
                else
                {
                    prmPassword.Value = obj.Password;
                }
                strWhere += " [Password] LIKE @Password OR ";
                lstParameters.Add(prmPassword);
            }





            if (obj.UserTypeID != -1)
            {
                DbParameter prmUserTypeID = con.createParameter();
                prmUserTypeID.ParameterName = "@UserTypeID";
                prmUserTypeID.DbType = DbType.Int64;
                if (obj.UserTypeID == null)
                {
                    prmUserTypeID.Value = DBNull.Value;
                }
                else
                {
                    prmUserTypeID.Value = obj.UserTypeID;
                }
                strWhere += " [UserTypeID]=@UserTypeID OR ";
                lstParameters.Add(prmUserTypeID);
            }





            if (obj.Mobile1 != "")
            {
                DbParameter prmMobile1 = con.createParameter();
                prmMobile1.ParameterName = "@Mobile1";
                prmMobile1.DbType = DbType.String;
                if (obj.Mobile1 == null)
                {
                    prmMobile1.Value = DBNull.Value;
                }
                else
                {
                    prmMobile1.Value = obj.Mobile1;
                }
                strWhere += " [Mobile1] LIKE @Mobile1 OR ";
                lstParameters.Add(prmMobile1);
            }





            if (obj.Mobile2 != "")
            {
                DbParameter prmMobile2 = con.createParameter();
                prmMobile2.ParameterName = "@Mobile2";
                prmMobile2.DbType = DbType.String;
                if (obj.Mobile2 == null)
                {
                    prmMobile2.Value = DBNull.Value;
                }
                else
                {
                    prmMobile2.Value = obj.Mobile2;
                }
                strWhere += " [Mobile2] LIKE @Mobile2 OR ";
                lstParameters.Add(prmMobile2);
            }





            if (obj.Phone1 != "")
            {
                DbParameter prmPhone1 = con.createParameter();
                prmPhone1.ParameterName = "@Phone1";
                prmPhone1.DbType = DbType.String;
                if (obj.Phone1 == null)
                {
                    prmPhone1.Value = DBNull.Value;
                }
                else
                {
                    prmPhone1.Value = obj.Phone1;
                }
                strWhere += " [Phone1] LIKE @Phone1 OR ";
                lstParameters.Add(prmPhone1);
            }





            if (obj.InnerPhone1 != "")
            {
                DbParameter prmInnerPhone1 = con.createParameter();
                prmInnerPhone1.ParameterName = "@InnerPhone1";
                prmInnerPhone1.DbType = DbType.String;
                if (obj.InnerPhone1 == null)
                {
                    prmInnerPhone1.Value = DBNull.Value;
                }
                else
                {
                    prmInnerPhone1.Value = obj.InnerPhone1;
                }
                strWhere += " [InnerPhone1] LIKE @InnerPhone1 OR ";
                lstParameters.Add(prmInnerPhone1);
            }





            if (obj.Phone2 != "")
            {
                DbParameter prmPhone2 = con.createParameter();
                prmPhone2.ParameterName = "@Phone2";
                prmPhone2.DbType = DbType.String;
                if (obj.Phone2 == null)
                {
                    prmPhone2.Value = DBNull.Value;
                }
                else
                {
                    prmPhone2.Value = obj.Phone2;
                }
                strWhere += " [Phone2] LIKE @Phone2 OR ";
                lstParameters.Add(prmPhone2);
            }





            if (obj.InnerPhone2 != "")
            {
                DbParameter prmInnerPhone2 = con.createParameter();
                prmInnerPhone2.ParameterName = "@InnerPhone2";
                prmInnerPhone2.DbType = DbType.String;
                if (obj.InnerPhone2 == null)
                {
                    prmInnerPhone2.Value = DBNull.Value;
                }
                else
                {
                    prmInnerPhone2.Value = obj.InnerPhone2;
                }
                strWhere += " [InnerPhone2] LIKE @InnerPhone2 OR ";
                lstParameters.Add(prmInnerPhone2);
            }





            if (obj.Language != null)
            {
                DbParameter prmLanguage = con.createParameter();
                prmLanguage.ParameterName = "@Language";
                prmLanguage.DbType = DbType.Int64;
                if (obj.Language == null)
                {
                    prmLanguage.Value = DBNull.Value;
                }
                else
                {
                    prmLanguage.Value = obj.Language;
                }
                strWhere += " [Language]=@Language OR ";
                lstParameters.Add(prmLanguage);
            }






            if (strWhere != "")
                strWhere = " where ( " + strWhere.Substring(0, strWhere.LastIndexOf("OR"));


            strWhere = strWhere + " ) AND [StatusID]<>'2'  order by [Name_EN] ";


            List<Persons> lstRet = Select(con, "", strWhere, lstParameters.ToArray());

            return lstRet;
        }




        /// <summary>
        /// this function will delete the employee and its data except if he is an operator and he has tickets it can't be deleted
        /// </summary>
        /// <param name="con">a classdatahandler having the database connections</param>
        /// <param name="obj">an object of type Persons that have the ID to delete</param>
        /// <returns>true or false (succeed or error)</returns>
        public bool deleteAll(ClassDataHandler con, Persons obj)
        {
            obj.StatusID = 2;


            PersonsDB objPersonsDB = new PersonsDB();

            foreach (Persons objChild in obj.lstChilds)
            {
                deleteAll(con, objChild);
            }

            objPersonsDB.Update(con, obj);


            return true;


        }





        
        
        /// <summary>
        /// this function will delete the employee and its data except if he is an operator and he has tickets it can't be deleted
        /// </summary>
        /// <param name="obj">an object of type Persons that have the ID to delete</param>
        /// <returns>true or false (succeed or error)</returns>
        public bool deleteAll(Persons obj)
        {
            ClassDataHandler con = new ClassDataHandler(ConnectionStrings.LMS);
            con.OpenTransConnection(IsolationLevel.ReadUncommitted);
            bool bRet = false;
            try
            {

                bRet = deleteAll(con, obj);


                con.CommitTransConnection();
            
            }
            catch
            {
                con.RollbackTransConnection();
                return false;
            }


            return bRet;

            
        }







    }

}

                        